package nzk.hssftemp;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.util.CellRangeAddress;

import javax.script.ScriptException;
import java.util.*;

/**
 * Simple one sheet spreadsheet document.
 *
 * @author N. Zulkarnaen (n.zulkarnaen@gmail.com)
 *         Date: 6/27/13
 *         Time: 6:01 PM
 * @version $Revision: 10 $
 */
public class HSSFTemplate {

    /**
     * sheet's name.
     */
    String sheetName = "Sheet1";

    /**
     * document's title.
     */
    String title = "HSSFTemplate";

    /**
     * company name (document's owner).
     */
    String company = "Your Company Name, PT";

    /**
     * row index counter.
     */
    int rows;

    /**
     * column index counter.
     */
    int cols;

    /**
     * maximal columns count.
     */
    int maxCol = 0;

    /**
     * row index on first detail iteration.
     */
    int dataStartRow = 0;

    /**
     * row index on last detail iteration.
     */
    int dataEndRow = 0;

    /**
     * detail number counter
     */
    int rowCount = 0;

    /**
     * built-in variable names
     */
    enum vars {
        _ROW_COUNT // detail's row number.
    }

    /**
     * document's data filter parameters
     */
    protected Parameter[] parameters;

    /**
     * initialize parameters with dummy data.
     */
    protected void initParameters() {
        parameters = new Parameter[]{
                new Parameter("Period", "01/01/2013 - 31/01/2013")
        };
    }

    /**
     * detail column's header
     */
    protected Column[] columns;

    /**
     * initialize columns with dummy data.
     */
    protected void initColumns() {
        columns = new Column[]{
                new Column()
        };
    }

    /**
     * collection of beans to fill detail's data (data bean must have all field
     * described in column's properties with correct data type).
     */
    Collection<?> list;

    /**
     * workbook which we will be build.
     */
    public HSSFWorkbook book = new HSSFWorkbook();

    /**
     * it is needed to applying formatting on cells.
     */
    DataFormat format = book.createDataFormat();

    /**
     * yup, every workbook need sheet.
     */
    HSSFSheet sheet = book.createSheet(sheetName);

    /**
     * current active row.
     */
    HSSFRow row;

    /**
     * move down to new row
     */
    protected void newRow() {
        row = sheet.createRow(rows++);
        if (cols - 1 > maxCol) maxCol = cols - 1;
        cols = 0;
    }

    /**
     * append a cell with value on the current row
     *
     * @param o     cell's value
     * @param style cell's style
     */
    protected void addCell(Object o, CSBase style) {
        HSSFCell cell = row.createCell(cols++);
        cell.setCellStyle(style.style);
        if (o instanceof Date) {
            cell.setCellValue((Date) o);
        } else if (o instanceof Double) {
            cell.setCellValue((Double) o);
        } else if (o instanceof Integer) {
            cell.setCellValue((Integer) o);
        } else {
            cell.setCellValue((String) o);
        }
    }

    /**
     * append an empty cell on the current row
     *
     * @param style cell's style
     */
    protected void addEmptyCell(CSBase style) {
        HSSFCell cell = row.createCell(cols++);
        cell.setCellStyle(style.style);
    }

    /**
     * append a cell with formula on the current row
     *
     * @param formula cell's formula
     * @param style   cell's style
     */
    protected void addCellWithFormula(String formula, CSBase style) {
        HSSFCell cell = row.createCell(cols++);
        cell.setCellStyle(style.style);
        cell.setCellFormula(formula);
    }

    /**
     * merge to left side cells.
     *
     * @param count of cell to merged.
     */
    protected void mergeLeft(int count) {
        int x = rows - 1;
        int y = cols - 1;
        final CellRangeAddress region = new CellRangeAddress(x, x, y - (count - 1), y);
        sheet.addMergedRegion(region);
    }

    /**
     * merge to right side cells.
     *
     * @param count of cell to merged.
     */
    protected void mergeRight(int count) {
        int x = rows - 1;
        int y = cols - 1;
        final CellRangeAddress region = new CellRangeAddress(x, x, y, y + (count - 1));
        sheet.addMergedRegion(region);
    }

    /**
     * create company's name cell
     */
    protected void createCompanyHeader() {
        newRow();
        addCell(company, new CSCompany(this));
    }

    /**
     * create document's title cell
     */
    protected void createTitle() {
        newRow();
        addCell(title, new CSBaseTitle(this));
    }

    /**
     * Create parameter cells. Once cell, one row for each parameter.
     */
    protected void createParameters() {
        initParameters();
        final CSParameter style = new CSParameter(this);
        for (Parameter parameter : parameters) {
            newRow();
            final String s = parameter.label + Parameter.SEPARATOR + parameter.value.toString();
            addCell(s, style);
        }
    }

    /**
     * Create detail column's header cells.
     */
	protected void createHeaderColumns() {
		initColumns();
		final CSDetailHeader styHeader = new CSDetailHeader(this);
		newRow();
		for (Column column : columns) {
			addCell(column.label, styHeader);
			if (column.properties.length > 1) {
				for (int i = 0; i < column.properties.length - 1; i++) {
					addEmptyCell(styHeader);
				}
				mergeLeft(column.properties.length);
			}
		}
	}

    /**
     * extract data values from a java bean using ColumnProp
     */
    protected Object extractValue(ColumnProp prop, Object data) {
        Object o;
        if (vars._ROW_COUNT.name().equals(prop.field)) {
            o = rowCount;
        } else {
            try {
                o = ReflectUtil.getFieldValue(prop.field, data);
            } catch (Exception e) {
                e.printStackTrace();
                o = "n/a";
            }
//            try {
//                o = ReflectUtil.invokeMethod(prop.field, data);
//            } catch (Exception e) {
//                try {
//                    o = ReflectUtil.getFieldValue(prop.field, data);
//                } catch (Exception ex) {
//                    ex.printStackTrace();
//                    o = "n/a";
//                }
//            }
        }
        return o;
    }

    /**
     * iterate through collections of data, extract values and create cell for them.
     */
    @SuppressWarnings("unchecked")
    protected void createValues() {
        dataStartRow = rows;
        rowCount = 0;
		// use this map to avoid exceeding excel's style limit
		Map<String, CSDetail> styleMap = new HashMap<String, CSDetail>();
        for (Object data : list) {
            rowCount++;
            newRow();
            for (Column col : columns) {
                for (ColumnProp prop : col.properties) {
                    Object o = "-";
                    try {
                        if (prop.isConditionMet(data)) {
                            o = extractValue(prop, data);
                        }
                    } catch (ScriptException e) {
                        e.printStackTrace();
                        o = e.getMessage();
                    }
					CSDetail csDetail = styleMap.get(prop.format);
					if (csDetail==null) {
						csDetail = new CSDetail(this, prop.format);
						styleMap.put(prop.format, csDetail);
					}
                    addCell(o, csDetail);
                }
            }
        }
        dataEndRow = rows - 1;
    }

    /**
     * create detail column footer containing data sum.
     *
     * @param labelCol     column index for 'TOTAL' label.
     * @param labelColSpan column span for label.
     * @param totals       column indexes for sum calculation.
     */
    public void createTotals(int labelCol, int labelColSpan, int[] totals) {
        newRow();
        cols = labelCol;
        addCell("TOTAL ", new CSDetailFooterLbl(this));
        if (labelColSpan < 0) {
            mergeLeft(labelColSpan);
        } else {
            mergeRight(labelColSpan);
        }
        final CSDetailFooter style = new CSDetailFooter(this);
        final int hi = dataStartRow + 1;
        final int lo = dataEndRow + 1;
        for (int i : totals) {
            cols = i;
            String cellRef = CellReference.convertNumToColString(cols);
            final String formula = Formula.sumUp(cellRef, hi, lo);
            addCellWithFormula(formula, style);
        }
    }

    /**
     * applying auto size calculation for every used column.
     */
    protected void autoSizeColumn() {
        for (int i = maxCol; i >= 0; i--) {
            sheet.autoSizeColumn(i);
        }
    }

    /**
     * call this method to build workbook.
     *
     * @throws NoSuchFieldException
     * @throws IllegalAccessException
     */
    public void build() throws NoSuchFieldException, IllegalAccessException {
        createCompanyHeader();
        createTitle();
        createParameters();
        createHeaderColumns();
        createValues();
        autoSizeColumn();
    }

    public HSSFTemplate(String title, List<?> list) {
        this.title = title;
        this.list = list;
    }

}
